IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageGetRoutes]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageGetRoutes]
GO
create procedure [dbo].[cms_sp_PageGetRoutes]
(
@SiteId int = null,
@SiteGroupId int = null,
@SystemLanguageId int = null,
@EntityTypeParameter nvarchar(400)
)
as
select 
	Pages.PageId,
	Pages.SiteGroupId,
	Pages.SiteId,
	Pages.SystemLanguageId,
	Pages.PublicPageId,
	Pages.PageTypeKey,
	Pages.PageBehaviourTypeKey,
	Pages.PageName,
	Pages.PageUrl,
	Pages.IsVisible,
	Pages.UseFileCache,
	Pages.CreatedBy,
	Pages.DateCreated,
	Pages.LastUpdatedBy,
	Pages.DateLastUpdated,
	Pages.PageKey
from dbo.Pages
inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @EntityTypeParameter
where EntityRevisions.IsLatestCompletedRevision = 1 and
	EntityRevisions.IsPublished = 1 and
	EntityRevisions.IsDeleted = 0
GO